﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using UFIDA.U9.Base;
using UFSoft.UBF.Util.DataAccess;

namespace UFIDA.U9.Cust.LS.PlugBE.utils
{
    internal class AFKUtils
    {
        /// <summary>
        /// 计算可用量,从数据库获取
        /// </summary>
        /// <param name="itemid">料品id</param>
        /// <param name="whs">仓库参数集合</param>
        /// <param name="projs">项目参数集合</param>
        /// <param name="projid">请购项目</param>
        /// <returns></returns>
        public static List<Dictionary<string,object>> GetKyl(long itemid, string whPath, string projPath, long projid)
        {
            //查询数据库
            string sql = $@"
select org.Code org,item.Code,item.Name
  -- 供给
  ,kc.sl kc_sl,qgzt.sl qgzt_sl,cgzt.sl cgzt_sl
  -- 需求
  ,qgxq.sl qgxq_sl,scwl.sl scwl_sl,xswf.sl xswf_sl,zfwl.sl zfwl_sl
  -- 可用量=供给-需求
  ,(isnull(kc.sl,0)+isnull(qgzt.sl,0)+isnull(cgzt.sl,0))-
  (isnull(qgxq.sl,0)+isnull(scwl.sl,0)+isnull(xswf.sl,0)+isnull(zfwl.sl,0)) kyl
from CBO_ItemMaster item
join Base_Organization org on item.Org=org.ID

-- 供给
left join (
-- 库存
SELECT 
   qoh.ItemInfo_ItemID item
   ,sum(qoh.StoreQty) sl
   FROM InvTrans_WhQoh qoh
   left join CBO_Wh wh on wh.ID=qoh.Wh
   left join CBO_Project proj on proj.ID=qoh.Project
   WHERE  1 = 1
    --and wh.Code in ('U20','H41','H40') -- 限定仓库
    --and proj.Code like 'BK%' -- 限定项目
    (({whPath} and {projPath}) or (proj.ID={projid})) --项目参数条件
    AND qoh.StoreQty!=0
    and isnull(qoh.Project,'')!=''
   group by qoh.ItemInfo_ItemID
) kc on kc.item=item.ID
left join (
-- 请购在途
SELECT 
   prline.ItemInfo_ItemID item
   ,sum(prline.ApprovedQtyTU - prline.TotalToPOQtyTU) sl -- 	请购在途=请购已核准数量-已转采购数量
   FROM PR_PR pr
   JOIN PR_PRLine prline ON prline.PR= pr.ID
   JOIN PR_PRDocType ty ON ty.id= pr.PRDocType
   JOIN CBO_Project proj on  proj.id=prline.project
   WHERE 1=1
   ({projPath} or proj.ID={projid}) --项目参数条件
   and prline.Status in(1) --核准中
   and ty.Code !='PR99' --不等于期初 
   and prline.DescFlexSegments_PrivateDescSeg8 = 'False'  -- 不消库算供给
   group by prline.ItemInfo_ItemID
) qgzt on qgzt.item=item.ID
left join (
-- 采购在途
SELECT  
   poline.ItemInfo_ItemID item
   ,sum(ship.PlanArriveQtyPU - ship.TotalRecievedQtyPU) sl --采购订单在途 = 采购订单核准数量-已收货数量
   FROM PM_PurchaseOrder po
   JOIN PM_POLine poline ON poline.PurchaseOrder= po.ID 
   JOIN PM_POShipLine ship ON poline.id= ship.POLine
   JOIN CBO_Project proj on  proj.id=ship.project
   JOIN PM_PODocType pty ON pty.id= po.DocumentType 
   WHERE 1=1
   ({projPath} or proj.ID={projid}) --项目参数条件
   and poline.Status in(0,1,2) --开立,核准中,已核准
   and pty.code!='PO99' 
   group by poline.ItemInfo_ItemID
) cgzt on cgzt.item=item.ID

-- 需求
left join (
-- 请购需求
SELECT 
   prline.ItemInfo_ItemID item
   ,isnull(sum(TRY_CAST(prline.DescFlexSegments_PrivateDescSeg9  AS DECIMAL(18,2))),0) sl  -- 	请购需求=手工需求数量
   FROM PR_PR pr
   JOIN PR_PRLine prline ON prline.PR= pr.ID
   JOIN PR_PRDocType ty ON ty.id= pr.PRDocType
   JOIN CBO_Project proj on  proj.id=prline.project
   WHERE 1=1
   and prline.Status in(1) --核准中
   and ty.Code !='PR99' --不等于期初 
   group by prline.ItemInfo_ItemID
) qgxq on qgxq.item=item.ID
left join (
-- 生产未领
select pick.ItemMaster item
  ,sum(pick.ActualReqQty) ActualReqQty --实际用量
  ,sum(pick.IssuedQty) IssuedQty --领料数量
  ,sum(pick.ActualReqQty-pick.IssuedQty) sl --未领数量
from MO_MOPickList pick
join MO_MO mo on mo.ID=pick.MO
where mo.DocState in (2) --开工
group by pick.ItemMaster
) scwl on scwl.item=item.ID
left join (
-- 销售未发
select line.ItemInfo_ItemID item
  ,sum(line.OrderByQtyTU) OrderByQtyTU
  ,sum(line.SOLineSumInfo_SumShipQtyTU) SOLineSumInfo_SumShipQtyTU
  ,sum(line.OrderByQtyTU-line.SOLineSumInfo_SumShipQtyTU) sl
from SM_SOLine line
join SM_SO doc on doc.ID=line.SO
where line.Status in (3) --已核准
group by line.ItemInfo_ItemID
) xswf on xswf.item=item.ID
left join (
--杂发未领
select line.ItemInfo_ItemID item
  ,sum(line.TradeUOMQty) sl
from InvDoc_MiscShipL line
join InvDoc_MiscShip doc on doc.ID=line.MiscShip
join CBO_Wh wh on wh.ID=line.Wh
where doc.Status in (0,1) --开立,核准中
  --and wh.Code in ('','') --限定仓库
group by line.ItemInfo_ItemID
) zfwl on zfwl.item=item.ID
where 1=1
  and org.Code='{Context.LoginOrg.Code}' 
  and item.ID='{itemid}'
";
            //run sql,查询可用量集合
            DataSet ds = new DataSet();
            DataAccessor.RunSQL(DataAccessor.GetConn(), sql, null, out ds);
            List<Dictionary<string, object>> retlist = DS2List(ds);

            return retlist;
        }

        private static List<Dictionary<string, object>> DS2List(DataSet ds)
        {
            //将集合翻译为map
            List<Dictionary<string, object>> retlist = new List<Dictionary<string, object>>();
            foreach (var row in ds.Tables[0].Rows)
            {
                DataRow row1 = (DataRow)row;
                Dictionary<string, object> retRow = new Dictionary<string, object>();
                foreach (var col in ds.Tables[0].Columns)
                {
                    DataColumn col1 = (DataColumn)col;
                    retRow.Add(col1.ColumnName, row1[col1.ColumnName]);
                }
                retlist.Add(retRow);
            }

            return retlist;
        }
    }
}
